﻿// ===================================
// Author : Micro.SanVey
// Eamil  : sanvey.china@gmail.com
// ===================================

namespace U.Help.Local
{
    #region 引用
    using System;
    using System.Collections.Generic;
    using System.IO.IsolatedStorage;
    using System.Linq;
    using System.Text;
    using System.Reflection;
    using System.Windows;
    using System.IO;
    using SQLiteClient;
    #endregion

    /// <summary>
    /// Sqlite 帮助类
    /// </summary>
    public class SQLiteHelper
    {
        string dbName;
        SQLiteConnection dbConn = null;

        public SQLiteHelper(string dbName)
        {
            this.dbName = dbName;

            IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication();
            if (!store.FileExists(dbName))
            {
                CopyDbFileToStorage(dbName);
            }
        }

        ~SQLiteHelper()
        {
            Close();
        }

        public int Insert<T>(T obj, string sqlcmd) where T : new()
        {
            try
            {
                Open();
                SQLiteCommand cmd = dbConn.CreateCommand(sqlcmd);
                int rec = cmd.ExecuteNonQuery(obj);
                return rec;
            }
            catch (SQLiteException ex)
            {
                System.Diagnostics.Debug.WriteLine("Insert failed: " + ex.Message);
                throw ex;
            }
        }

        public int Delete(string sqlcmd)
        {
            try
            {
                Open();
                SQLiteCommand cmd = dbConn.CreateCommand(sqlcmd);
                return cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                System.Diagnostics.Debug.WriteLine("Deletion failed: " + ex.Message);
                throw ex;
            }
        }

        public List<T> SelectList<T>(String sqlcmd) where T : new()
        {
            try
            {
                Open();
                SQLiteCommand cmd = dbConn.CreateCommand(sqlcmd);
                var lst = cmd.ExecuteQuery<T>();
                return lst.ToList<T>();
            }
            catch (SQLiteException ex)
            {
                System.Diagnostics.Debug.WriteLine("Select Failed: " + ex.Message);
                throw ex;
            }
        }

        private void Open()
        {
            if (dbConn == null)
            {
                dbConn = new SQLiteConnection(dbName);
                dbConn.Open();
            }
        }

        private void Close()
        {
            if (dbConn != null)
            {
                dbConn.Dispose();
                dbConn = null;
            }
        }

        /// <summary>
        /// 从资源文件中赋值到隔离存储空间
        /// </summary>
        /// <param name="dbName"></param>
        public void CopyDbFileToStorage(string dbName)
        {
            using (var store = IsolatedStorageFile.GetUserStoreForApplication())
            {
                Uri uri = new Uri(@"/USpell;component/"+dbName, UriKind.Relative);
                using (var src = Application.GetResourceStream(uri).Stream)
                {
                    using (var dest = new IsolatedStorageFileStream(dbName, FileMode.OpenOrCreate, FileAccess.Write, store))
                    {
                        src.CopyTo(dest);
                    }
                }
            }
        }
    }
}
